package cj.web.vote.dao;
/*
 *  
 *  
*/
import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.vote.domain.Vote;

/**
 * 
 * @author 
 *
 */
@DAO
public interface VoteDAO{
	
	@SQL("SELECT * FROM Vote WHERE 1=1 "
	    	    				    		        	+ " AND name=:t.name,"
	        			    		        	+ " AND description=:t.description,"
	        			    		        	+ " AND startDate=:t.startDate,"
	        			    		        	+ " AND endDate=:t.endDate,"
	        			    		        	+ " AND deleteFlag=:t.deleteFlag,"
	        			    		        	+ " AND createAT=:t.createAT,"
	        			    		        	+ " AND updateAT=:t.updateAT"
	        				+" ORDER BY update_date DESC LIMIT :t.start,:t.end ")
    List<Vote> query(@SQLParam("t") Vote vote);
    
    @SQL("select * from Vote where id=:id")
    Vote queryById(String id);
    
    @SQL("SELECT * FROM Vote WHERE startDate < date_format(:1,'%Y-%m-%d %H:%i:%s') and endDate > date_format(:1,'%Y-%m-%d %H:%i:%s') order by createAT desc limit 1")
    Vote queryNowVote(String date);
    
    @SQL("SELECT B.song_id,B.song_name,B.song_url,B.song_img,B.singer_name,B.singer_university,B.voteSongId ,(COUNT(A.wxOpenid)+B.votePlus) AS vote_num from VoteLog A RIGHT OUTER JOIN (SELECT A.id AS song_id,A.name AS song_name,A.songURL AS song_url,A.imgURL AS song_img,A.singer AS singer_name,A.university AS singer_university,B.id AS voteSongId,A.votePlus FROM Song A ,(SELECT	*	FROM	VoteSongRef WHERE voteId = :1 AND type = :2 LIMIT :3) B  WHERE A.id = B.songId)B ON A.voteSongID = B.voteSongId GROUP BY B.voteSongId ORDER BY vote_num DESC,B.song_id ASC")
    List<Map> querySongListFromVote(String voteId,String type,int limit);
    
    @SQL("INSERT INTO Vote ("
    		        		+ "id,"
			        		+ "name,"
			        		+ "description,"
			        		+ "startDate,"
			        		+ "endDate,"
			        		+ "deleteFlag,"
			        		+ "createAT,"
			        		+ "updateAT"
							+") VALUES ("
							+ ":t.id,"
							+ ":t.name,"
							+ ":t.description,"
							+ ":t.startDate,"
							+ ":t.endDate,"
							+ ":t.deleteFlag,"
							+ ":t.createAT,"
							+ ":t.updateAT"
					+")")
    void create(@SQLParam("t") Vote vote);
	
	@SQL("UPDATE Vote SET "
		    		    						    			        + "name=:t.name,"
		        					    			        + "description=:t.description,"
		        					    			        + "startDate=:t.startDate,"
		        					    			        + "endDate=:t.endDate,"
		        					    			        + "deleteFlag=:t.deleteFlag,"
		        					    			        + "createAT=:t.createAT,"
		        					    			        + "updateAT=:t.updateAT"
		        						+ " WHERE id=:t.id")
    void update(@SQLParam("t") Vote vote);
    
    @SQL("UPDATE Vote SET deleteFlag=0 WHERE id=:id")
    void deleteById(String id);
}